# 以下代码为使用Python内置geopy判断并根据经纬度生成地理位置的代码

import pandas as pd
import re
from geopy.geocoders import Nominatim
from geopy.exc import GeocoderTimedOut
import math

# 读取CSV文件
df = pd.read_excel('geography.xls')

# 删除空行
df.dropna(axis=0, how='all', inplace=True)

# 重置索引
df.reset_index(drop=True, inplace=True)

# 创建geolocator对象
geolocator = Nominatim(user_agent="myGeocoder")

# 定义查询函数
def get_location(lat, lon):
    if math.isnan(lat) or math.isnan(lon):
        return "Invalid coordinates"
    try:
        location = geolocator.reverse((lat, lon), timeout=10)
        formatted_location = format_location(location.address)
        return formatted_location
    except GeocoderTimedOut:
        return "Geocoder Timed Out"

def format_location(address):
    pattern = re.compile(r'([\u4e00-\u9fa5]+省)?([\u4e00-\u9fa5]+市)([\u4e00-\u9fa5]+区)([\u4e00-\u9fa5]+[路|街|巷|道])')
    match = pattern.search(address)
    if match:
        return ''.join(match.groups())
    else:
        return address

# 查询地理位置
df['地理位置'] = df.apply(lambda row: get_location(row['纬度'], row['经度']), axis=1)

# 输出结果
for index, row in df.iterrows():
    print(f"{row['纬度']}\t{row['经度']}")
    print(row['地理位置'])
    print()

# 导出到EXCEL文件
df.to_excel('geopy_result.xlsx', index=False)